package com.hnas.oracle.dao.impl;

import java.math.BigDecimal;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Component;

import com.hnas.core.dao.HntBaseDaoImpl;
import com.hnas.oracle.dao.CommomOracleDao;
import com.hnas.oracle.dto.DTOHntEmployeeInfo;
import com.hnas.oracle.orm.Mrch;

@Component("commomOracleDao")
public class CommomOracleDaoImpl extends HntBaseDaoImpl<Mrch> implements CommomOracleDao{

	public CommomOracleDaoImpl() {
		super(Mrch.class);
	}

	@Override
	public String getOrganFullNameByEmpId(String empId) {
		String sql = "select organ.full_name from m_base_info_employeeinfo emp left join m_base_info_organinfo organ "
				+ "on emp.node_id=organ.node_id where emp.employee_id='"+empId+"' order by organ.opt_date desc";
		Query query = this.entityManager.createNativeQuery(sql);
		List<Object> objects = query.getResultList();
		if(null!=objects&&objects.size()>0){
			return (String) objects.get(0);
		}
		return null;
	}

	@Override
	public String getCardTypeByEmpId(String empId) {
		String sql = "select card.car_type from m_base_info_employeeinfo emp left join m_base_employee_card card on emp.employee_id=card.employee_id "
				+ "where emp.employee_id='"+empId+"' order by card.CARD_DATE desc";
		Query query = this.entityManager.createNativeQuery(sql);
		List<Object> objects = query.getResultList();
		if(null!=objects&&objects.size()>0){
			return ((BigDecimal) objects.get(0)).toString();
		}
		return null;
	}

	@Override
	public String getNewestCardNo(String empId) {
		//String sql = "select card_no from m_base_employee_card where employee_id='"+empId+"' order by card_date desc nulls last";
		String sql = "select a.card_no from m_base_employee_card a "
				+ "left join m_card_user_info b on a.card_no = b.card_id"
				+ " where a.employee_id = '"+empId+"' and b.card_stat = '03' order by"
				+ " a.card_date desc nulls last";
		
		Query query = this.entityManager.createNativeQuery(sql);
		List<Object> objects = query.getResultList();
		if(null!=objects&&objects.size()>0){
			return (String) objects.get(0);
		}
		return null;
	}

	@Override
	public String getAccnoByCardNo(String cardNo,String subCode) {
		String sql = "select distinct(p.acc_no) from hnt_dev.m_acct_certificate_re r left join hnt_dev.m_acct_payment_info p on r.payment_id = p.payment_id where r.card_id = ?1 and r.item_stat = '1' and p.payment_stat = '1'";
		Query query = this.entityManager.createNativeQuery(sql);
		query.setParameter(1, cardNo);
		List<Object> objects = query.getResultList();
		if(objects == null || objects.size() <= 0) return null;
		if(objects.size() == 1) return (String) objects.get(0);
		return null;
	}

	@Override
	public DTOHntEmployeeInfo getEmployeeInfoByCardNo(String cardNo) {
		String sql = "select distinct t1.employee_id, t1.account from hnt_dev.m_base_info_employeeinfo t1 left join hnt_dev.m_base_employee_card t2 on t1.employee_id = t2.employee_id where t2.card_no = ?1";
		Query query = this.entityManager.createNativeQuery(sql);
		query.setParameter(1, cardNo);
		List<Object[]> objects = query.getResultList();
		if(objects == null || objects.size() <= 0) return null;
		if(objects.size() == 1){
			Object[] result = objects.get(0);
			DTOHntEmployeeInfo empInfo = new DTOHntEmployeeInfo();
			empInfo.setEmployeeId(String.valueOf(result[0]));
			empInfo.setAccName(String.valueOf(result[1]));
			return empInfo;
		}
		return null;
	}

	public Long getCurrentTime()  throws Exception {
		String sql = "select ((sysdate - TO_DATE('19700101', 'yyyymmdd')) * 86400 -TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone), 1, 3)) * 3600) d from dual";
		Query query = this.entityManager.createNativeQuery(sql);
		List<Object> objects = query.getResultList();
		if(null!=objects&&objects.size()>0){
			if(null != objects.get(0)){
				String time = objects.get(0).toString().split("\\.")[0];
				return Long.valueOf(time);
			}
		}
		return null;
	}
	
}
